using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using MosziNET.Persistence.Properties;
using System.Data;
using System.ComponentModel;

namespace MosziNET.Persistence
{
    /// <summary>
    /// The main class that performs database operations
    /// </summary>
    public class PersistenceManager : IDisposable
    {
        #region / CommandType /

        enum CommandType
        {
            Select,
            Insert,
            Delete,
            Find,
            Update
        }

        #endregion / CommandType /

        #region / Private fields /

        static PersistenceManager _instance;

        string connectionString;
        Dictionary<string, SqlCommand> commandCache;

        private const string primaryKeyParameter = "@pkValue";

        #endregion / Private fields /

        #region / Constructors /

        /// <summary>
        /// Creates a new instance of the <c>PersistenceManager</c> class
        /// </summary>
        /// <param name="connectionString">The connection string to be used for db operations</param>
        public PersistenceManager(string connectionString)
        {
            this.connectionString = connectionString;
            this.commandCache = new Dictionary<string, SqlCommand>();
        }

        /// <summary>
        /// Creates the persistence manager without a connection string
        /// </summary>
        public PersistenceManager() : this(null)
        {
        }

        /// <summary>
        /// Provides single access to the persistance manager
        /// </summary>
        public static PersistenceManager Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new PersistenceManager();
                }

                return _instance;
            }
        }

        #endregion / Constructors /

        #region / Public methods /

        /// <summary>
        /// Loads the specified entity from the persistence medium
        /// </summary>
        /// <typeparam name="T">The entity type</typeparam>
        /// <typeparam name="PKType">The primary key type of the entity</typeparam>
        /// <param name="primaryKey">The primary key of the entty</param>
        /// <returns>A loaded entity</returns>
        public T Load<T, PKType>(PKType primaryKey)
        {
            // creates an instance of the specified type
            T entity = Activator.CreateInstance<T>();

            SqlCommand selCmd = GetCommand<T, PKType>(CommandType.Select, true);
            selCmd.Parameters[primaryKeyParameter].Value = primaryKey;
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                selCmd.Connection = con;
                con.Open();
                SqlDataReader reader = selCmd.ExecuteReader();

                bool useOnlyMarkedFields = UseOnlyMarkedFields(typeof(T));
                if (reader.Read())
                {
                    FillEntity<T>(entity, reader, useOnlyMarkedFields);
                }
                else
                    throw new EntityNotFoundException(
                        String.Format(Resources.Exception_EntityNotFound, primaryKey));
            }

            // we prepared the entity so return it
            return entity;
        }

         /// <summary>
        /// Returns a collection of entities matching the specified criteria
        /// </summary>
        /// <typeparam name="T">The entity type</typeparam>
        /// <param name="condition">The condition to use</param>
        /// <param name="orderStatement">The order statement. Can be null</param>
        /// <returns></returns>
        public T[] Find<T>(FindCondition condition, OrderStatement orderStatement)
        {
            return Find<T>(condition, orderStatement, true, -1);
        }

        /// <summary>
        /// Returns a collection of entities matching the specified criteria
        /// </summary>
        /// <typeparam name="T">The entity type</typeparam>
        /// <param name="condition">The condition to use</param>
        /// <param name="orderStatement">The order statement. Can be null</param>
        /// <param name="useCommandCache">Specifies whether the method should use a command cache</param>
        /// <param name="maxRecords">Specifies the maximum number of items to return</param>
        /// <returns></returns>
        public T[] Find<T>(FindCondition condition, OrderStatement orderStatement,
            bool useCommandCache, int maxRecords)
        {
            List<T> resultList = new List<T>();

            // build the find command
            SqlCommand findCmd = GetCommand<T, object>(CommandType.Find,
                useCommandCache, condition, orderStatement, maxRecords);

            string[] parameters = condition.Parameters;
            
            // notice the usage of index based parameters and 
            // parameters[i]
            // we are relying on the fact that a cached command will have 
            // in the same order the parameters all the time
            for (int i = 0; i < parameters.Length; i++)
            {
                findCmd.Parameters[i].Value =
                    condition.GetParameterValue(parameters[i]);
            }

            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                findCmd.Connection = con;
                con.Open();
                SqlDataReader reader = findCmd.ExecuteReader();
                bool useOnlyMarkedFields = UseOnlyMarkedFields(typeof(T));
                while (reader.Read())
                {
                    T entity = Activator.CreateInstance<T>();
                    FillEntity<T>(entity, reader, useOnlyMarkedFields);
                    resultList.Add(entity);
                }
            }

            return resultList.ToArray();
        }

        /// <summary>
        /// Inserts an entity to the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="PKType"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public PKType Insert<T, PKType>(T entity)
        {
            SqlCommand cmd = GetCommand<T, PKType>(CommandType.Insert, true);
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                FillCommand<T>(cmd, entity);
                cmd.Connection = con;
                con.Open();
                
                PKType pkValue;
                object returnValue = cmd.ExecuteScalar();
                
                if (DBNull.Value.Equals(returnValue))
                    pkValue = (new PKType[1])[0];
                else
                    pkValue = (PKType) Convert.ChangeType(returnValue, typeof(PKType));
                con.Close();

                return pkValue;
            }
        }

        /// <summary>
        /// Updates an entity in the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="PKType"></typeparam>
        /// <param name="entity"></param>
        public void Update<T, PKType>(T entity)
        {
            SqlCommand cmd = GetCommand<T, PKType>(CommandType.Update, true);
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                // update all the parameters
                FillCommand<T>(cmd, entity);
                
                // set the primary key
                cmd.Parameters[primaryKeyParameter].Value =
                    GetPrimaryKeyValue<T, PKType>(entity);

                cmd.Connection = con;
                con.Open();
                
                if (cmd.ExecuteNonQuery() == 0)
                    throw new EntityNotFoundException(String.Format(
                        Resources.Exception_EntityNotFound, 
                        GetPrimaryKeyValue<T, PKType>(entity)));

                con.Close();
            }
        }

        /// <summary>
        /// Deletes the specified entity
        /// </summary>
        /// <typeparam name="T">The entity type</typeparam>
        /// <typeparam name="PKType">The primary key type</typeparam>
        /// <param name="primaryKey">The primary key value</param>
        public void Delete<T, PKType>(PKType primaryKey)
        {
            SqlCommand cmd = GetCommand<T, PKType>(CommandType.Delete, true);
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                cmd.Parameters[primaryKeyParameter].Value = primaryKey;
                cmd.Connection = con;
                con.Open();
                if (cmd.ExecuteNonQuery() == 0)
                    throw new EntityNotFoundException(String.Format(
                        Resources.Exception_EntityNotFound, primaryKey));

                con.Close();
            }
        }

        /// <summary>
        /// Executes the custom query and returns the first row of the result
        /// </summary>
        /// <param name="customQuery"></param>
        /// <returns></returns>
        public List<object> ExecuteCustomQuery(string customQuery)
        {
            List<object> returnList = new List<object>();

            using (SqlCommand cmd = new SqlCommand(customQuery))
            {
                using (SqlConnection con = new SqlConnection(this.ConnectionString))
                {
                    cmd.Connection = con;
                    con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            returnList.Add(reader.GetValue(i));
                        }
                    }

                    con.Close();
                }
            }

            return returnList;
        }

        #endregion / Public methods /

        #region / Public properties /

        /// <summary>
        /// Gets/sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (String.IsNullOrEmpty(this.connectionString))
                    throw new ArgumentNullException();

                return this.connectionString;
            }
            set
            {
                this.connectionString = value;
            }
        }

        #endregion / Public properties /

        #region / Helper methods /

        /// <summary>
        /// Returns a value indicating wether to use or not the PersistedAttribute
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        private bool UseOnlyMarkedFields(Type entityType)
        {
            TableMappedAttribute[] attributes = (TableMappedAttribute[]) entityType.GetCustomAttributes(
                typeof(TableMappedAttribute), true);
            if (attributes.Length == 1)
            {
                return ((TableMappedAttribute)attributes[0]).UseOnlyMarkedMembers;
            }

            return false;
        }

        /// <summary>
        /// Uses the data reader to read all the fields for the entity
        /// </summary>
        /// <typeparam name="T">The entity type</typeparam>
        /// <param name="entity">The entity itself</param>
        /// <param name="reader">The reader to be used</param>
        private void FillEntity<T>(T entity, SqlDataReader reader, bool useOnlyMarkedFields)
        {
            FieldInfo[] fields = typeof(T).GetFields(BindingFlags.Instance | 
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
            for (int i = 0; i < fields.Length; i++)
            {
                if (PersistedField(fields[i], useOnlyMarkedFields))
                    FillField<T>(entity, fields[i], reader);
            }
        }

        /// <summary>
        /// Fills the commands parameters from the specified entity
        /// </summary>
        /// <typeparam name="T">The entity's type</typeparam>
        /// <param name="cmd">The command to fill</param>
        /// <param name="entity">The entity to be used when filling the parameters</param>
        private void FillCommand<T>(SqlCommand cmd, T entity)
        {
            FieldInfo[] fields = typeof(T).GetFields(BindingFlags.Instance |
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
            for (int i = 0; i < fields.Length; i++)
            {
                if (!ReadOnlyField(fields[i]) && PersistedField(fields[i]))
                {
                    string paramName = "@" + GetFieldColumn(fields[i]);
                    object tempValue = fields[i].GetValue(entity);

                    cmd.Parameters[paramName].Value = (tempValue == null ? DBNull.Value : tempValue);
                }
            }
        }

        /// <summary>
        /// Returns if a field should be persisted or not
        /// </summary>
        /// <param name="fieldInfo"></param>
        /// <param name="useOnlyMarkedFields"></param>
        /// <returns></returns>
        private bool PersistedField(FieldInfo fieldInfo, bool useOnlyMarkedFields)
        {
            if (!useOnlyMarkedFields)
            {
                return fieldInfo.GetCustomAttributes(typeof(NotPresistedAttribute), true).Length == 0;
            }
            else
            {
                return fieldInfo.GetCustomAttributes(typeof(PersistedFieldAttribute), true).Length != 0;
            }
        }

        /// <summary>
        /// Returns if a field should be persisted or not
        /// </summary>
        /// <param name="fieldInfo"></param>
        /// <returns></returns>
        private bool PersistedField(FieldInfo fieldInfo)
        {
            TableMappedAttribute[] attributes =
                (TableMappedAttribute[]) fieldInfo.DeclaringType.GetCustomAttributes(
                typeof(TableMappedAttribute), true);

            if (attributes.Length == 0)
                return PersistedField(fieldInfo, false);
            else
                return PersistedField(fieldInfo, ((TableMappedAttribute)attributes[0]).UseOnlyMarkedMembers);
        }

        /// <summary>
        /// Returns if a field should be written to the db or not
        /// </summary>
        /// <param name="fieldInfo"></param>
        /// <returns></returns>
        private bool ReadOnlyField(FieldInfo fieldInfo)
        {
            if (fieldInfo.GetCustomAttributes(typeof(ReadOnlyAttribute), true).Length != 0)
                return true;

            return false;
        }

        private void FillField<T>(T entity, FieldInfo aField, SqlDataReader reader)
        {
            string columnName = GetFieldColumn(aField);
            int ordinal;
            try
            {

                ordinal = reader.GetOrdinal(columnName);
            }
            catch (IndexOutOfRangeException ex)
            {
                throw new MetaDataException(String.Format(
                    Resources.Exception_ColumnNotFound, columnName), ex);
            }

            // handle the null value
            object tempValue = reader.GetValue(ordinal);
            if (System.DBNull.Value.Equals(tempValue))
                tempValue = null;

            // finally set the value to the property
            aField.SetValue(entity, tempValue);
        }

        /// <summary>
        /// Gets (from the cache if possible) the command for
        /// the specified entity and operation type
        /// </summary>
        /// <typeparam name="T">The type of the entity</typeparam>
        /// <param name="type">The operation type</param>
        /// <param name="additionalParam">Additional properties</param>
        /// <param name="useCache">Use or not cache</param>
        /// <returns>The sql command</returns>
        private SqlCommand GetCommand<T, PKType>(CommandType type, 
            bool useCache, params object[] additionalParam)
        {
            SqlCommand aCommand = null;

            // build the command's key
            string commandKey;
            switch(type)
            {
                case CommandType.Find:
                    string orderStatement = String.Empty;
                    if (additionalParam[1] != null)
                        orderStatement = ((OrderStatement)additionalParam[1]).OrderStatementText;

                    commandKey = String.Format("{0}_{1}_{2}_{3}_{4}", typeof(T).Name, type, 
                        ((FindCondition)additionalParam[0]).CacheKey,
                        orderStatement, 
                        (int)additionalParam[2]);
                    break;
                default:
                    commandKey = String.Format("{0}_{1}", typeof(T).Name, type);
                    break;
            }
            
            if (!commandCache.ContainsKey(commandKey))
            {
                lock (commandCache)
                {
                    if (!commandCache.ContainsKey(commandKey))
                    {
                        switch (type)
                        {
                            case CommandType.Select:
                                aCommand = GetSelectCommand<PKType>(typeof(T));
                                break;
                            case CommandType.Find:
                                aCommand = GetFindCommand<T>(
                                    (FindCondition)additionalParam[0],
                                    (OrderStatement)additionalParam[1],
                                    (int)additionalParam[2]);
                                break;
                            case CommandType.Insert:
                                aCommand = GetInsertCommand<T>();
                                break;
                            case CommandType.Delete:
                                aCommand = GetDeleteCommand<T, PKType>();
                                break;
                            case CommandType.Update:
                                aCommand = GetUpdateCommand<T, PKType>();
                                break;
                        }

                        if (useCache)
                        {
                            // cache the command for later use
                            commandCache.Add(commandKey, aCommand);
                        }
                    }
                }
            }

            if (aCommand == null)
                aCommand = commandCache[commandKey];

            return aCommand;
        }

        /// <summary>
        /// Returns the requested delete command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private SqlCommand GetDeleteCommand<T, PKType>()
        {
            string deleteStatement = @"DELETE {0} WHERE {1} = {2}";

            Type entityType = typeof(T);

            SqlCommand deleteCommand = new SqlCommand();
            deleteCommand.CommandText = String.Format(deleteStatement, 
                GetWriteObjectName(entityType), 
                GetPrimaryKeyName(entityType), 
                primaryKeyParameter);
            deleteCommand.Parameters.Add(primaryKeyParameter,
                MapSqlType(typeof(PKType)));
            deleteCommand.CommandType = System.Data.CommandType.Text;

            return deleteCommand;
        }

        /// <summary>
        /// Generates an insert command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private SqlCommand GetInsertCommand<T>()
        {
            string insertStatement = "INSERT {0} ({2}) VALUES ({1}) SELECT @@IDENTITY";
            SqlCommand insertCommand = new SqlCommand();

            List<string> paramNames = new List<string>();
            List<string> columnNames = new List<string>();

            FieldInfo[] fields = typeof(T).GetFields(BindingFlags.Instance |
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
            for (int i = 0; i < fields.Length; i++)
            {
                if (!ReadOnlyField(fields[i]) && PersistedField(fields[i]))
                {
                    string fieldColumn = GetFieldColumn(fields[i]);
                    string paramName = "@" + fieldColumn;
                    
                    paramNames.Add(paramName);
                    columnNames.Add(fieldColumn);

                    insertCommand.Parameters.Add(paramName, MapSqlType(fields[i].FieldType));
                }
            }

            insertCommand.CommandText = String.Format(insertStatement,
                GetWriteObjectName(typeof(T)),
                String.Join(",", paramNames.ToArray()),
                String.Join(",", columnNames.ToArray()));
            insertCommand.CommandType = System.Data.CommandType.Text;

            return insertCommand;
        }

        /// <summary>
        /// Generates an update command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="PKType"></typeparam>
        /// <returns></returns>
        private SqlCommand GetUpdateCommand<T, PKType>()
        {
            string updateStatement = "UPDATE {0} SET {1} WHERE {2} = {3}";
            SqlCommand updateCommand = new SqlCommand();

            string updateList = String.Empty;
            FieldInfo[] fields = typeof(T).GetFields(BindingFlags.Instance |
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);

            for (int i = 0; i < fields.Length; i++)
            {
                if (!ReadOnlyField(fields[i]) && PersistedField(fields[i]))
                {
                    string fieldColumn = GetFieldColumn(fields[i]);
                    string paramName = "@" + fieldColumn;
                    updateCommand.Parameters.Add(paramName, MapSqlType(fields[i].FieldType));

                    // generate the SET statement
                    if (String.IsNullOrEmpty(updateList))
                        updateList = fieldColumn + " = " + paramName;
                    else
                        updateList += ", " + fieldColumn + " = " + paramName;
                }
            }
            
            // finally add an input parameter for the primary key too
            updateCommand.Parameters.Add(primaryKeyParameter, MapSqlType(typeof(PKType)));

            // finally generate the command text
            updateCommand.CommandText = String.Format(updateStatement,
                GetWriteObjectName(typeof(T)),
                updateList, GetPrimaryKeyName(typeof(T)), primaryKeyParameter);

            updateCommand.CommandType = System.Data.CommandType.Text;

            return updateCommand;
        }

        /// <summary>
        /// Returns the base select command
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        private SqlCommand GetSelectBaseCommand(Type entityType)
        {
            string selectStatement = @"SELECT * FROM {0}";

            SqlCommand selectCommand = new SqlCommand();
            selectCommand.CommandText = String.Format(
                selectStatement, GetReadObjectName(entityType));
            selectCommand.CommandType = System.Data.CommandType.Text;

            return selectCommand;
        }

        /// <summary>
        /// Returns a base select command with an optional TOP statement
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        private SqlCommand GetSelectBaseCommand(Type entityType, int maxRecords)
        {
            string selectStatement = @"SELECT {0} * FROM {1}";

            SqlCommand selectCommand = new SqlCommand();
            selectCommand.CommandText = String.Format(
                selectStatement, 
                maxRecords > -1 ? " TOP " + maxRecords.ToString() : String.Empty,
                GetReadObjectName(entityType));
            selectCommand.CommandType = System.Data.CommandType.Text;

            return selectCommand;
        }

        /// <summary>
        /// Returns the select command
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        private SqlCommand GetSelectCommand<PKType>(Type entityType)
        {
            SqlCommand selectCmd = GetSelectBaseCommand(entityType);

            string whereClause = " WHERE {0} = {1}";
            selectCmd.CommandText = selectCmd.CommandText +
                String.Format(whereClause,
                    GetPrimaryKeyName(entityType), primaryKeyParameter);

            selectCmd.Parameters.Add(primaryKeyParameter, MapSqlType(typeof(PKType)));

            return selectCmd;
        }

        /// <summary>
        /// Builds a find command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        private SqlCommand GetFindCommand<T>(FindCondition condition, 
            OrderStatement orderBy, int maxRecords)
        {
            // set up the command text
            SqlCommand findCmd = GetSelectBaseCommand(typeof(T), maxRecords);
            string orderStatement = String.Empty;
            if (orderBy != null)
                orderStatement = orderBy.OrderStatementText.Trim();

            if (!String.IsNullOrEmpty(orderStatement))
            {
                findCmd.CommandText = String.Format("{0} WHERE {1} ORDER BY {2}",
                    findCmd.CommandText, condition.ConditionText, orderStatement);
            }
            else
            {
                findCmd.CommandText = String.Format("{0} WHERE {1}",
                    findCmd.CommandText, condition.ConditionText);
            }

            // set up the parameters
            string[] parameters = condition.Parameters;
            for (int i = 0; i < parameters.Length; i++)
            {
                object value = condition.GetParameterValue(parameters[i]);
                findCmd.Parameters.Add(parameters[i], MapSqlType(value.GetType()));
            }

            return findCmd;
        }

        /// <summary>
        /// Returns the object that should be used for reading this entity
        /// </summary>
        /// <param name="entityType">The type of the entity</param>
        /// <returns></returns>
        private string GetReadObjectName(Type entityType)
        {
            TableMappedAttribute[] tableMapped =
                (TableMappedAttribute[])entityType.GetCustomAttributes(typeof(TableMappedAttribute), true);

            if (tableMapped.Length == 1)
                return tableMapped[0].TableName;

            // read the attributes
            ReadObjectAttribute[] readObject =
                (ReadObjectAttribute[])entityType.GetCustomAttributes(typeof(ReadObjectAttribute), true);

            if (readObject.Length == 1)
                return readObject[0].ReadObject;

            throw new MetaDataException(Resources.Exception_MultipleReadObjects);
        }

        /// <summary>
        /// Returns the object that should be used to update or create the entity
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        private string GetWriteObjectName(Type entityType)
        {
            TableMappedAttribute[] tableMapped =
                (TableMappedAttribute[])entityType.GetCustomAttributes(typeof(TableMappedAttribute), true);

            if (tableMapped.Length == 1)
                return tableMapped[0].TableName;

            // read the attributes
            WriteObjectAttribute[] writeObject =
                (WriteObjectAttribute[])entityType.GetCustomAttributes(typeof(WriteObjectAttribute), true);

            if (writeObject.Length == 1)
                return writeObject[0].WriteObject;

            throw new MetaDataException(Resources.Exception_MultipleWriteObjects);

        }

        /// <summary>
        /// Returns the primary key name of the entity
        /// </summary>
        /// <param name="entityType">The type of the entity</param>
        /// <returns></returns>
        private string GetPrimaryKeyName(Type entityType)
        {
            FieldInfo[] fields = entityType.GetFields(BindingFlags.Instance |
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
            foreach (FieldInfo aField in fields)
            {
                if (aField.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length != 0)
                    return GetFieldColumn(aField);
            }

            // at this point throw an exception
            throw new MetaDataException(Resources.Exception_NoPrimaryKey);
        }

        /// <summary>
        /// Returns the primary key value of the entity
        /// </summary>
        /// <param name="entity">The entity</param>
        /// <returns></returns>
        private PKType GetPrimaryKeyValue<T, PKType>(T entity)
        {
            Type entityType = typeof(T);

            FieldInfo[] fields = entityType.GetFields(BindingFlags.Instance |
                BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);
            foreach (FieldInfo aField in fields)
            {
                if (aField.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length != 0)
                    return (PKType) aField.GetValue(entity);
            }

            // at this point throw an exception
            throw new MetaDataException(Resources.Exception_NoPrimaryKey);
        }

        /// <summary>
        /// Returns the column name to be used for a field
        /// </summary>
        /// <param name="aField"></param>
        /// <returns></returns>
        private string GetFieldColumn(FieldInfo aField)
        {
            return aField.Name;
        }

        /// <summary>
        /// Maps the managed type to an sql type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private SqlDbType MapSqlType(Type type)
        {
            if (type == typeof(int))
                return SqlDbType.Int;
            if (type == typeof(string))
                return SqlDbType.NVarChar;
            if (type == typeof(DateTime))
                return SqlDbType.DateTime;
            if (type == typeof(bool))
                return SqlDbType.Bit;
            if (type == typeof(byte[]))
                return SqlDbType.Image;

            // if this type is a nullable type then return the nullable type's base type
            if (type.IsGenericType)
            {
                Type[] arguments = type.GetGenericArguments();
                return MapSqlType(arguments[0]);
            }

            throw new ArgumentException(String.Format(Resources.Exception_UnknownType, type.Name));
        }

        #endregion / Helper methods /

        #region / IDisposable Members /

        /// <summary>
        /// Disposes the instance
        /// </summary>
        public void Dispose()
        {
            lock (commandCache)
            {
                foreach(SqlCommand aCommand in commandCache.Values)
                {
                    aCommand.Dispose();
                }
                commandCache = null;
            }
        }

        #endregion / IDisposable Members /
    }


}
